/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.pdx.cs.guavadata.query;

import edu.pdx.cs.guavadata.DBConnection;
import edu.pdx.cs.guavadata.Transaction;
import edu.pdx.cs.guavadata.condition.Condition;
import edu.pdx.cs.guavadata.data.Common;
import edu.pdx.cs.guavadata.domain.Domain;
import edu.pdx.cs.guavadata.function.SqlFunctionDefinition;
import edu.pdx.cs.guavadata.subclass.SemanticUnit;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.lang3.StringUtils;

/**
 *
 * @author VuTran
 */
public class SqlProvider implements Provider {

    HashSet<String> fkWorkList;

    // contains the column mapped to the table
    private Map<String, List<String>> columnsPerTable;
    private Map<String, List<String>> keyColumnsPerTable;
    private List<String> foreignKeys;
    private int iCount = 0;
    private List<String> triggers;
    private boolean foundFK = false;

    // Building foreign keys: data structures to remember where Tier 1/2 foreign keys point.
    private Map<String, Map<String, List<ForeignKey>>> foreignKeyTable;
    
    public StopWatch sw;
    public StopWatch prov;

    private String serializeTableAndColumns(String tableName, List<String> columns) {
        Collections.sort(columns);
        return tableName + "@" + StringUtils.join(columns.toArray(), "$");
    }

    private String serializeConditions(List<String> column, List<Condition> eqCondition) {
        List<String> strings = new ArrayList<String>();
        for (int i = 0; i < column.size(); i++) {
            strings.add(column.get(i) + eqCondition.get(i).toString());
        }
        Collections.sort(strings);
        return StringUtils.join(strings.toArray(), "|");
    }

    private ForeignKey isTier1FKSource(String tableName, List<String> columns) {
        return isTier1FKSource(serializeTableAndColumns(tableName, columns));
    }

    private ForeignKey isTier1FKSource(String key) {
        // Key does not exist yet
        if (!foreignKeyTable.containsKey(key))
            return null;

        Map<String, List<ForeignKey>> conditionMap = foreignKeyTable.get(key);

        // Assume that the conditionmap dictionary is not empty
        if (conditionMap.size() != 1)
            // More logic than just a single FK, so not Tier 1
            return null;

        // Since we know there is only one, separate the one that we have
        Object fkConditions = conditionMap.keySet().toArray()[0];

        // Test to see if the list of conditions is empty - if not, not Tier 1
        if (fkConditions.toString().length() != 0)
            return null;

        // Get the foreign key statements
        List<ForeignKey> fks = conditionMap.get(fkConditions);

        // If more than one fk statement for the condition set, not Tier 1
        if (fks.size() > 1)
            return null;
        ForeignKey fk = fks.get(0);

        // Test the target fk to see if the parent has conditions
        if (fk.getParentConditionColumns().size() != 0)
            return null;

        // Test the target fk to see if the parent columns cover the parent primary key
        if (fk.getParentColumns().size() != keyColumnsPerTable.get(fk.getParentTable()).size())
            return null;

        return fk;
    }

    private List<ForeignKey> aggragateForeignKeys(String key) {
        List<ForeignKey> fkList = new ArrayList<ForeignKey>();
        Map<String, List<ForeignKey>> condFKMap = foreignKeyTable.get(key);
        for (String condition : condFKMap.keySet()) {
            for (ForeignKey fk : condFKMap.get(condition))
                fkList.add(fk);
        }
        return fkList;
    }

    private SqlText createTier2ForeignKey(String key) {
        SqlText sql = new SqlText();

        // Group foreignkeys for processing
        List<ForeignKey> fkList = aggragateForeignKeys(key);

        String childTable = fkList.get(0).childTable;

        // see if fk or trigger is already there if so drop it
        if (foreignKeys.contains(key)) {
            DBConnection.executeNonQuery("ALTER TABLE " + childTable + " DROP CONSTRAINT " + key);
            foreignKeys.remove(key);
        }
        if (triggers.contains(key)) {
            DBConnection.executeNonQuery("DROP TRIGGER " + key);
        } else {
            triggers.add(key);
        }

        String com = "CREATE TRIGGER " + key + " ON ";

        ForeignKey firstFK = fkList.get(0);

        List<String> selectCols = new ArrayList<String>();
        for (int i = 0; i < firstFK.childColumns.size(); i++) {
            selectCols.add("UNIONED@SELECTS." + firstFK.childColumns.get(i));
        }

        HashSet<String> parentTables = new HashSet<String>();
        for (int i = 0; i < fkList.size(); i++) {
            parentTables.add(fkList.get(i).parentTable);
        }

        com = com + childTable +
            " INSTEAD OF INSERT AS DECLARE @count int SELECT @count=count(*) FROM INSERTED I@ WHERE NOT EXISTS (SELECT "
                + StringUtils.join(selectCols.toArray(), ", ") + " FROM ";
        // +firstFK.ChildTable + ", " + String.Join(", ", parentTables.ToArray()) + ", ";


        List<String> selectList = new ArrayList<String>();
        List<String> fkConditions = new ArrayList<String>();

        // create selects from fks
        for (int i = 0; i < fkList.size(); i++) {
            List<String> colList = new ArrayList<String>();
            for (int j = 0; j < fkList.get(i).parentColumns.size(); j++)
                colList.add(fkList.get(i).parentColumns.get(j) + " as " + fkList.get(i).childColumns.get(j));
            // create parent conditions
            List<String> parentConditions = new ArrayList<String>();
            for (int j = 0; j < fkList.get(i).parentConditionColumns.size(); j++)
                parentConditions.add(fkList.get(i).parentConditionColumns.get(i) +
                        fkList.get(i).getParentConditions().get(i).toString());

            selectList.add("SELECT " + StringUtils.join(colList.toArray(), ", ") + " FROM " +
                    fkList.get(i).parentTable + (parentConditions.size() > 0 ?
                        " WHERE " + StringUtils.join(parentConditions.toArray(), ", ") : ""));

            // create condition Strings while we're at it
            String childConditions = childConditionsFromFK(fkList.get(i));
            if (!childConditions.equals(""))
                fkConditions.add(childConditions);
        }

        List<String> joinConditions = new ArrayList<String>();

        // create join conditions
        com = com + "(" + StringUtils.join(selectList.toArray(), " UNION ") + ") AS UNIONED@SELECTS";
        for (int i = 0; i < fkList.get(0).childColumns.size(); i++)
            joinConditions.add("I@." + fkList.get(0).childColumns.get(i) + "=UNIONED@SELECT." +
                    fkList.get(0).childColumns.get(i));
        com = com + " WHERE " + StringUtils.join(joinConditions.toArray(), " AND ");

        if (fkConditions.size() > 0) {
            com = com + " AND (" + StringUtils.join(fkConditions.toArray(), " OR ") + ")";
        }

        sql.command = com + ")\n IF(@count=0)\n insert into "+ childTable
                + " select * from INSERTED\n ELSE raiserror('Foreign key violation',18,1)";

        return sql;
    }

    private String childConditionsFromFK(ForeignKey fk) {
        List<String> conditions = new ArrayList<String>();

        // add child conditions
        for (int i = 0; i < fk.childConditionColumns.size(); i++)
            conditions.add("I@." + fk.childConditionColumns.get(i) + fk.childConditions.get(i).toString());

        if (conditions.size() > 0)
            return "(" + StringUtils.join(conditions.toArray(), " AND ") + ")";

        return "";
    }

    private SqlText createTier1ForeignKey(ForeignKey fk) {
        SqlText sql = new SqlText();
        String fkName = serializeTableAndColumns(fk.childTable, fk.childColumns);

        // if fk already exists, punt
        if (foreignKeys.contains(fkName))
            return null;

        // create simply a foreign key constraint
        sql.command = "ALTER TABLE " + fk.childTable + " ADD CONSTRAINT " + fkName + 
                " FOREIGN KEY (" + StringUtils.join(fk.childColumns.toArray(), ", ") + ") REFERENCES " + fk.parentTable + " (" + StringUtils.join(fk.parentColumns.toArray(), ", ") + ")" +
                " ON DELETE CASCADE ON UPDATE CASCADE";

        foreignKeys.add(fkName);
        return sql;
    }

    public SqlProvider() {
        keyColumnsPerTable = new HashMap<String, List<String>>();
        foreignKeyTable = new HashMap<String, Map<String, List<ForeignKey>>>();
        fkWorkList = new HashSet<String>();
        sw = new StopWatch();
        prov = new StopWatch();
    }

    private void getCurrentSchema() {
        columnsPerTable = new HashMap<String, List<String>>();
        foreignKeys = new ArrayList<String>();
        triggers = new ArrayList<String>();
        ResultSet cols = DBConnection.getColumns();
        ResultSet fks = DBConnection.getForeignKeys();
        ResultSet trigs = DBConnection.getTriggers();

        try {
            while (cols.next()) {
                String s = cols.getString("TABLE_NAME");
                if (!columnsPerTable.containsKey(s))
                    columnsPerTable.put(s, new ArrayList<String>());
                columnsPerTable.get(s).add(cols.getString("COLUMN_NAME"));
            }

            while (fks.next()) {
                foreignKeys.add(fks.getString("CONSTRAINT_NAME"));
            }

            while (trigs.next()) {
                triggers.add(trigs.getString("name"));
            }
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
    }

    public ResultSet process(Transaction t) {
        // if we are preseeding there is no need to process the query
        if (t.getContents().size() > 0) {
            if (t.getContents().get(0).root instanceof Filter) {
                Filter f = (Filter) t.getContents().get(0).root;
                if (f.getConditionString().contains("'-__-'"))
                    return null;
            }
        }   

        if (this.columnsPerTable == null)
            getCurrentSchema();

        for (Command c : t.getContents()) {
            prov.start();
            SqlText sql = c.root.visit(this);
            prov.stop();

            if (sql != null && !(c.root.isQuery())) {
                sw.start();
                DBConnection.executeNonQuery(sql.toString());
                sw.stop();
            } else if (sql != null) {
                sw.start();
                ResultSet retVal = DBConnection.executeQuery(sql.toString());
                sw.stop();
                return retVal;
            }
        }

       // getCurrentSchema();

        if (foundFK) {
            processForeignKey();
            foundFK = true;
        }
        return null;
    }

    private void processForeignKey() {
        SqlText sql = null;
        for (String key : fkWorkList) {
            ForeignKey fk = isTier1FKSource(key);
            if (fk != null) {
                sql = createTier1ForeignKey(fk);
            } else {
                sql = createTier2ForeignKey(key);
            }

            if (sql != null) {
                sw.start();
                DBConnection.executeNonQuery(sql.toString());
                sw.stop();
            }
            sql = null;
        }
    }

    public SqlText transform(AddColumn ac) {
        // Determine if the column should be added
        if (this.columnsPerTable.get(ac.table).contains(ac.column))
            return null;

        SqlText sql = new SqlText();
        sql.command = "ALTER TABLE " + ac.table + " ADD " + ac.column + " " + ac.domain.getDBType() + " NULL";
        return sql;
    }

    public SqlText transform(AddElement ae) {
        return null;
    }

    public SqlText transform(AddTable at) {
        // Add to memory teh key columns of the table
        keyColumnsPerTable.put(at.getTable(), at.getKeys());

        SqlText sql = new SqlText();
        List<String> cols = at.getColumns();
        List<Domain> domains = at.getDomains();

        // Determine if we need to add the table
        if (!columnsPerTable.containsKey(at.getTable())) {
            sql.command = "CREATE TABLE " + at.getTable() + " (" + cols.get(0) + " " + domains.get(0).getDBType() +
                    ((at.getKeys().contains(cols.get(0)) && at.enforcePrimaryKey) ? " NOT NULL" : " NULL") + ", ";
            for (int i = 1; i < cols.size(); i++) {
                sql.command = sql.command + cols.get(i) + " " + domains.get(i).getDBType() +
                    ((at.getKeys().contains(cols.get(i)) && at.enforcePrimaryKey) ? " NOT NULL" : " NULL") + ", ";
            }

            if (at.enforcePrimaryKey && at.getKeys().size() > 0) {
                sql.command = sql.command + "CONSTRAINT pk_" + at.getTable() + " PRIMARY KEY CLUSTERED (" +
                        StringUtils.join(at.getKeys().toArray(), ", ") + "))";
            } else {
                sql.command = sql.command.substring(0, sql.command.length() - 2) + ")";
            }
            columnsPerTable.put(at.getTable(), at.getColumns());
        } else {
            List<String> currentColumns = columnsPerTable.get(at.getTable());
            
            // We need to add the columns individually, if necessary, because we already have the table present
            for (int i = 0; i < cols.size(); i++) {
                if (currentColumns.contains(cols.get(i)))
                    continue;
                
                // create the add column statement
                sql.command = sql.command + "ALTER TABLE " + at.getTable() + " ADD " + 
                        cols.get(i) + " " + domains.get(i).getDBType() + ";";
                
                columnsPerTable.get(at.getTable()).add(cols.get(i));
            }
            if (sql.command.equals(""))
                // trim the last semicolon
                sql.command = sql.command.substring(0, sql.command.length() - 1);
        }

        if (sql.command == "")
            return null;
        return sql;
    }

    public SqlText transform(AlterColumnRename acr) {
        SqlText sql = new SqlText();
        sql.command = "EXEC sp_rename '" + acr.table + "." + acr.oldName + "', '" + acr.newName + "', 'COLUMN'";
        return sql;
    }

    public SqlText transform(AlterElementRename aer) {
        SqlText sql = new SqlText();
        sql.command = "UPDATE " + aer.table + " SET " + aer.column + "=" + Common.wrap(aer.newName) +
                " WHERE " + aer.column + "=" + Common.wrap(aer.oldName);
        return sql;
    }

    public SqlText transform(AlterTableRename atr) {
        SqlText sql = new SqlText();
        sql.command = "EXEC sp_rename '" + atr.getOldName() + "', '" + atr.getNewName() + "'";
        return sql;
    }

    public SqlText transform(AntiSemiJoin asj) {
        SqlText st1 = asj.child1.visit(this);
        SqlText st2 = asj.child2.visit(this);
        SqlText keep, pitch;

        // Determine which side of the join is the persistent side
        if (asj.type == JoinType.LEFT) {
            keep = st1;
            pitch = st2;
        } else {
            keep = st2;
            pitch = st1;
        }

        // Construct the return value
        pitch.clearProjection();
        pitch.where = ((pitch.where.equals("")) ? "" : ("(" + pitch.where + ") AND ")) + asj.getJoinCondition();
        keep.where = ((keep.where.equals("")) ? "" : ("(" + keep.where + ") AND ")) + "NOT EXISTS " + pitch.toString();
        return keep;
    }

    public SqlText transform(ApplyOp ap) {
        try {
            if (ap.f instanceof SqlFunctionDefinition) {
                List<String> translatedInputs = new ArrayList<String>();
                SqlText sql = ap.child.visit(this);
                for (String s : ap.input) {
                    translatedInputs.add(sql.get(s));
                    sql.remove(s);
                }

                List<String> output = ap.output;
                for (int i = 0; i < output.size(); i++)
                    sql.add(output.get(i), ((SqlFunctionDefinition) ap.f).sqlString(i, (String[]) translatedInputs.toArray()));
                return sql;
            }
            throw new Exception("Functions that do not have an Sql Function Definition defined are not currently supported.");
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
        return null;
    }

    public SqlText transform(Check ch) {
        return new SqlText();
    }

    public SqlText transform(CrossJoin cj) {
        SqlText st1 = cj.child1.visit(this);
        SqlText st2 = cj.child2.visit(this);

        if (st1.from.equals("") && st2.from.equals(""))
            st1.from = "(" + st1.from + " CROSS JOIN " + st2.from + ")";
        else if (st2.from.equals(""))
            st1.from = st2.from;

        // Join the where clauses
        if (st1.where.equals("") && st2.where.equals(""))
            st1.where = "(" + st1.where + ") AND (" + st2.where + ")";
        else if (st2.where.equals(""))
            st1.where = st2.where;

        try {
            for (String s : st2.projectAliases)
                if (!(st1.projectAliases.contains(s)))
                    st1.add(s, st2.get(s));
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
        return st1;
    }

    public SqlText transform(Delete d) {
        SqlText sql = new SqlText();
        sql.command = "DELETE FROM " + d.table;
        if (d.conditions.size() == 0) {
            return sql;
        }

        // Combine the conditions into a string with AND semantics
        sql.command = sql.command + " WHERE " + d.columns.get(0) + d.conditions.get(0).toString();
        for (int i = 1; i < d.conditions.size(); i++) {
            sql.command = sql.command + " AND " + d.columns.get(i) + d.conditions.get(i).toString();
        }
        return sql;
    }

    public SqlText transform(DropColumn dc) {
        SqlText sql = new SqlText();
        sql.command = "ALTER TABLE " + dc.table + " DROP COLUMN " + dc.column;

        // Update internals
        this.columnsPerTable.get(dc.table).remove(dc.column);
        return sql;
    }

    public SqlText transform(DropElement de) {
        SqlText sql = new SqlText();

        // Determine if we need to set to null or to delete rows
        if (keyColumnsPerTable.get(de.table).contains(de.column))
            sql.command = "DELETE FROM " + de.table + " WHERE " + de.column + "=" + Common.wrap(de.element);
        else
            sql.command = "UPDATE " + de.table + " SET " + de.column + "=NULL WHERE "
                    + de.column + "=" + Common.wrap(de.element);
        return sql;
    }

    public SqlText Transform(DropForeignKey dfk) {
        foundFK = true;
        return new SqlText();
    }

    public SqlText transform(DropTable dt) {
        SqlText sql = new SqlText();
        sql.command = "DROP TABLE " + dt.table;

        // Update internals
        this.keyColumnsPerTable.remove(dt.table);
        this.columnsPerTable.remove(dt.table);

        return sql;
    }

    public SqlText Transform(Error er) {
        SqlText sql = new SqlText();
        sql.command = "DECLARE @count int SELECT @count=count(*) FROM (";
        sql.command += er.query.visit(this).toString() + ") AS Temp; IF @count>0 raiserror('Failed error check',18,1)";
        return new SqlText();
    }

    public SqlText transform(Except e) {
        // Create the except string
        String text = "(" +
            ((e.child1 instanceof RowConstant) ? "SELECT " + ((RowConstant) e.child1).getAsColumnText() : e.child1.visit(this).toString()) +
            " EXCEPT " +
            ((e.child2 instanceof RowConstant) ? "SELECT " + ((RowConstant) e.child2).getAsColumnText() : e.child2.visit(this).toString()) +
            ")";
        if (e.alias != null)
            text = text + " AS " + e.alias;
        else
        {
            text = text + " AS temp_" + iCount;
            iCount++;
        }

        // Create and return the full query
        SqlText sql = new SqlText();
        sql.from = text;
        for (String s : e.exposedColumns)
            sql.add(s, "");

        return sql;
    }

    public SqlText transform(Filter f) {
        SqlText st = f.child.visit(this);
        if (st.where.equals(""))
            st.where = f.getConditionString();
        else
            st.where = "(" + st.where + ")" + " AND " + f.getConditionString();
        return st;
    }

    public SqlText transform(ForeignKey fk) {
        foundFK = true;
        //Add to list to process all at the end
        fkWorkList.add(serializeTableAndColumns(fk.childTable, fk.childColumns));

        String tablekey = serializeTableAndColumns(fk.childTable, fk.childColumns);
        String conditionkey = serializeConditions(fk.childConditionColumns, fk.childConditions);

        if (!foreignKeyTable.keySet().contains(tablekey))
            foreignKeyTable.put(tablekey, new HashMap<String, List<ForeignKey>>());

        Map<String, List<ForeignKey>> conditionDictionary = foreignKeyTable.get(tablekey);
        if (conditionDictionary.containsKey(conditionkey))
            conditionDictionary.get(conditionkey).add(fk);
        else
        {
            List<ForeignKey> newList = new ArrayList<ForeignKey>();
            newList.add(fk);
            conditionDictionary.put(conditionkey, newList);
        }

        return null;
    }

    public SqlText transform(Insert i) {
        SqlText sql = new SqlText();
        sql.command = "INSERT INTO " + i.table + " (" +
                StringUtils.join(i.columns.toArray(), ",") + ") " + i.child.visit(this).toString();
        return sql;
    }

    public SqlText transform(Intersect i) {
        // Create the intersect string
        String text = "(" +
            ((i.children.get(0) instanceof RowConstant) ?
                "SELECT " + ((RowConstant) i.children.get(0)).getAsColumnText() :
                i.children.get(0).visit(this).toString());
        for (int j = 1; j < i.children.size(); j++)
            text = text + " INTERSECT " +
                ((i.children.get(j) instanceof RowConstant) ?
                    "SELECT " + ((RowConstant) i.children.get(j)).getAsColumnText() :
                    i.children.get(j).visit(this).toString());
        text = text + ")";
        if (i.alias != null)
            text = text + " AS " + i.alias;
        else
        {
            text = text + " AS temp_" + iCount;
            iCount++;
        }

        // Create and return the full query
        SqlText sql = new SqlText();
        sql.from = text;
        for (String s : i.exposedColumns)
            sql.add(s, "");

        return sql;
    }

    public SqlText transform(Join j) {
        SqlText st1 = j.child1.visit(this);
        SqlText st2 = j.child2.visit(this);
        st1.from = "(" + st1.from + " " + j.type.toString() + " JOIN " + st2.from + " ON " + j.getJoinCondition() + ") ";
        // Join the where clauses
        if (st1.where.equals("") && st2.where.equals("")) {
            st1.where = "(" + st1.where + ") AND (" + st2.where + ")";
        } else if (st2.where.equals("")) {
            st1.where = st2.where;
        }
        try {
            for (String s : st2.projectAliases) {
                if (!(st1.projectAliases.contains(s))) {
                    st1.add(s, st2.get(s));
                }
            }
        } catch (Exception ex) {
            System.err.println("ERROR: " + ex.toString());
        }
        return st1;

    }

    public SqlText transform(Operator o) {
        try {
            throw new Exception("Unknown operator of type " + o.getClass().getName() + " found.");
        } catch (Exception ex) {
            System.err.println("ERROR: " + ex.toString());
        }
        return null;
    }

    public SqlText transform(PivotOp po) {
        // Transform the child reference
        SqlText sql = po.child.visit(this);

        // Wrap the FROM clause in the unpivot clause
        SqlText pivot = new SqlText();
        Map<String, String> keyMap = po.keyMap;
        for (String k : keyMap.keySet()) {
            pivot.add(keyMap.get(k), po.newAlias + "." + k);
        }

        for (String k : po.foldValues.keySet()) {
            pivot.add(po.foldValues.get(k), "[" + k + "]");
        }
        pivot.from = "(" + sql.toString() + ") AS " + po.newAlias + " PIVOT (max(" + po.newAlias + "." + po.valCol
                + ") FOR " + po.newAlias + "." + po.attCol + " IN " + po.getPivotValueList() + ") AS " + po.newAlias;
        return pivot;
    }

    public SqlText transform(Project p) {
        SqlText st = p.child.visit(this);
        List<String> toDelete = new ArrayList<String>();

        // Build a list of all of the columns that need to be dropped
        for (String s : st.projectAliases)
            if (!(p.exposedColumns.contains(s)))
                toDelete.add(s);

        // Drop the columns
        for (String s : toDelete)
            st.remove(s);
        return st;
    }

    public SqlText transform(RowConstant rc) {
        SqlText sql = new SqlText();
        for (int i = 0; i < rc.exposedColumns.size(); i++)
            sql.add(rc.exposedColumns.get(i), Common.wrap(rc.fields.get(i)));
        return sql;
    }

    public SqlText transform(TableRef tr) {
        SqlText st = new SqlText();
        st.from = tr.table + " AS " + tr.alias;
        for (int i = 0; i < tr.columns.size(); i++)
            st.add(tr.exposedColumns.get(i), tr.alias + "." + tr.columns.get(i));
        return st;
    }

    public SqlText transform(Union u) {
        // Create the union string
        String text = "(" +
            ((u.children.get(0) instanceof RowConstant) ? "SELECT " + ((RowConstant) u.children.get(0)).getAsColumnText() : u.children.get(0).visit(this).toString());
        for (int i = 1; i < u.children.size(); i++)
            text = text + " UNION " +
                ((u.children.get(i) instanceof RowConstant) ? "SELECT " + ((RowConstant) u.children.get(i)).getAsColumnText() : u.children.get(i).visit(this).toString());
        text = text + ")";
        if (u.alias != null)
            text = text + " AS " + u.alias;
        else {
            text = text + " AS temp_" + iCount;
            iCount++;
        }

        // Create and return the full query
        SqlText sql = new SqlText();
        sql.from = text;
        for (String s : u.exposedColumns)
            sql.add(s, "");

        return sql;
    }

    public SqlText transform(UnpivotOp uo) {
        // Transform the child reference
        SqlText sql = uo.child.visit(this);

        // Wrap the FROM clause in the unpivot clause
        sql.from = "(" + sql.from + ") UNPIVOT (" + uo.valCol + " FOR " + uo.attCol + " IN (" +
                StringUtils.join(uo.foldColumns.toArray(), ",") + ")) AS " + uo.newAlias;
        return sql;
    }

    public SqlText transform(Update up) {
        if (up.columns.size() <= 0)
                return null;

        SqlText sql = up.child.visit(this);
        SqlText sqlCommand = new SqlText();

        // Two situations: update has self-join or not
        // If there is a self-join (to pick up on key values), we need to pick up the join attributes and join table
        if (up.needJoin()) {
            sql.from = up.table + " LEFT JOIN " + sql.from + " ON " + up.getJoinString();
            if (sql.where.equals(""))
                sql.where = up.getConditionString();
            else if (up.getConditionString().equals(""))
                sql.where = "(" + sql.where + ")" + " AND (" + up.getConditionString() + ")";
        }
        else if (!up.getConditionString().equals(""))
            sql.where = up.getConditionString();

        sqlCommand.command = sql.toUpdateString(up.table, up.columns);
        return sqlCommand;
    }

    public List<SemanticUnit> semanticUnits(String table) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

}
